using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using DevExpress.XtraEditors;
using DevExpress.XtraEditors.Controls;

namespace LibraryManagement.ThongKeSach
{
    class ThongKeSach_CTL
    {
        DataSet ds;
        DataTable dt;
        ThongKeSach_DAO tks_dao;
        ComboBoxEdit cbe;
        ComboBoxItemCollection coll;
        public ThongKeSach_CTL()
        {
            ds = new DataSet();
            dt = new DataTable();
            tks_dao = new ThongKeSach_DAO();
            cbe = new ComboBoxEdit();
            coll = cbe.Properties.Items;
        }
        public ComboBoxEdit LoadNam()
        {
            DataTable dt = new DataTable();
            tks_dao.KetNoi();
            ds = tks_dao.truyvan("select distinct year(ngaymuon) from phieumuon");
            dt = ds.Tables[0];
            string []nam = new string[dt.Rows.Count];
            coll.BeginUpdate();
            for (int i = 0; i < dt.Rows.Count; i++)
                coll.Add(dt.Rows[i][0].ToString());
            coll.EndUpdate();
            cbe.SelectedIndex = -1;
            return cbe;
        }
        public ComboBoxEdit LoadNamTheoThang(int thang)
        {
            DataTable dt = new DataTable();
            coll.Clear();
            cbe.Properties.Items.Clear();
            ds = tks_dao.truyvan("select distinct year(ngaymuon) from phieumuon where month(ngaymuon)=" + thang);
            dt = ds.Tables[0];
            coll.BeginUpdate();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                coll.Add(dt.Rows[i][0].ToString());
            }
            coll.EndUpdate();
            return cbe;
        }
        public DataTable LoadThongKe(int thang,int nam)
        {
            DataTable dt = new DataTable();
            ds = tks_dao.truyvan("select theloaisach.ten,theloaisach.ten,count(*),theloaisach.ten from theloaisach,sach,dausach,phieumuon,chitietphieumuon where sach.madausach=dausach.madausach and dausach.matheloaisach=theloaisach.matheloaisach and maphieumuong=maphieumuon and chitietphieumuon.masach = sach.masach and year(ngaymuon)="+nam+" and month(ngaymuon)="+thang+" group by theloaisach.ten,theloaisach.matheloaisach");
            dt = ds.Tables[0];
            int stt = 1;
            double tongsoluong = 0;
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                tongsoluong = tongsoluong + double.Parse(dt.Rows[i][2].ToString());
            }
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                dt.Rows[i][0] = stt++;
                dt.Rows[i][3] =String.Format("{0:0.##}",double.Parse(dt.Rows[i][2].ToString()) / tongsoluong*100) +"%";
            }
            return dt;

        }
    }
}
